﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using VPSoftTestApplication.Entity;

namespace VPSoftTestApplication.DAL
{
    public class InventoryControler : AbstractInventoryControler
    {
        public InventoryControler(string connectionString)
        {
            _connectionString = connectionString;
        }

        private List<Inventory> ReadFromCommand(SqlCeCommand command)
        {
            List<Inventory> inventories = new List<Inventory>();
            using (SqlCeDataReader dr = command.ExecuteReader())
            {
                int iid = dr.GetOrdinal("IID");
                int documentHaderId = dr.GetOrdinal("DocumentHeaderIID");
                int barcode = dr.GetOrdinal("Barcode");
                int quantityOriginal = dr.GetOrdinal("QuantityOriginal");
                int createDate = dr.GetOrdinal("CreateDate");
                int inputType = dr.GetOrdinal("InputType");
                int quantityEdit = dr.GetOrdinal("QuantityEdit");
                int quantityDifference = dr.GetOrdinal("QuantityDifference");
                int product = dr.GetOrdinal("Product");
                while (dr.Read())
                {
                    Inventory inventory = new Inventory();
                    inventory.IID = dr.GetGuid(iid);
                    inventory.DocumentHeaderIID = dr.GetGuid(documentHaderId);
                    inventory.Barcode = dr.IsDBNull(barcode) ? string.Empty : dr.GetString(barcode);
                    inventory.QuantityOriginal = dr.IsDBNull(quantityOriginal) ? 0d : dr.GetDouble(quantityOriginal);
                    inventory.CreateDate = dr.IsDBNull(createDate) ? DateTime.MinValue : dr.GetDateTime(createDate);
                    inventory.InputType = dr.IsDBNull(inputType) ? string.Empty : dr.GetString(inputType);
                    inventory.QuantityEdit = dr.IsDBNull(quantityEdit) ? 0d : dr.GetDouble(quantityEdit);
                    inventory.QuantityDifference = dr.IsDBNull(quantityDifference)
                                                       ? 0d
                                                       : dr.GetDouble(quantityDifference);
                    inventory.Product = dr.IsDBNull(product) ? string.Empty : dr.GetString(product);
                    inventories.Add(inventory);

                }
                return inventories;
            }
        }

        public override void Insert(Inventory inventory)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                using (SqlCeCommand command = connection.CreateCommand())
                {
                    command.CommandText = "INSERT INTO Inventors " +
                                          "(IID, DocumentHeaderIID, Barcode, QuantityOriginal, CreateDate, InputType, QuantityEdit, QuantityDifference, Product) " +
                                          "VALUES (NEWID(),@DocumentHeaderIID,@Barcode,@QuantityOriginal,@CreateDate,@InputType,@QuantityEdit,@QuantityDifference,@Product)";
                    command.Parameters.Add("@DocumentHeaderIID", inventory.DocumentHeaderIID);
                    command.Parameters.Add(new SqlCeParameter() {ParameterName = "@Barcode", Value = inventory.Barcode ?? string.Empty});
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@QuantityOriginal", Value = inventory.QuantityOriginal });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@CreateDate", Value = inventory.CreateDate });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@InputType", Value = inventory.InputType ?? string.Empty });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@QuantityEdit", Value = inventory.QuantityEdit });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@QuantityDifference", Value = inventory.QuantityDifference });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@Product", Value = inventory.Product ?? string.Empty });
                    if (command.ExecuteNonQuery()!= 1)
                    {
                        throw new Exception("Not Inserted");
                    }
                }
            }
        }

        public override void Update(Inventory newInventory)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                using (SqlCeCommand command = connection.CreateCommand())
                {
                    command.CommandText = "UPDATE Inventors SET " +
                                          "DocumentHeaderIID = @DocumentHeaderIID, Barcode =@Barcode, QuantityOriginal = @QuantityOriginal, CreateDate =@CreateDate, InputType =@InputType, QuantityEdit =@QuantityEdit, QuantityDifference =@QuantityDifference, Product = @Product " +
                                          "where IId = @IID";
                    command.Parameters.Add("@DocumentHeaderIID", newInventory.DocumentHeaderIID);
                    command.Parameters.Add("@IID", newInventory.IID);
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@Barcode", Value = newInventory.Barcode ?? string.Empty });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@QuantityOriginal", Value = newInventory.QuantityOriginal });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@CreateDate", Value = newInventory.CreateDate });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@InputType", Value = newInventory.InputType ?? string.Empty });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@QuantityEdit", Value = newInventory.QuantityEdit });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@QuantityDifference", Value = newInventory.QuantityDifference });
                    command.Parameters.Add(new SqlCeParameter() { ParameterName = "@Product", Value = newInventory.Product ?? string.Empty });
                    if (command.ExecuteNonQuery() != 1)
                    {
                        throw new Exception("Not Inserted");
                    }
                }
            }
        }

        public override void Delete(Inventory inventory)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                using (SqlCeCommand command = connection.CreateCommand())
                {
                    command.CommandText = "delete Inventors " +
                                          "where IID = @ID";
                    command.Parameters.Add("@ID", inventory.IID);
                    if (command.ExecuteNonQuery() != 1)
                    {
                        throw new Exception("Not Deleted");
                    }
                }
            }
        }

        public override int ListCount(DocumentHeader documentHeader, int pageSize)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                using (SqlCeCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT COUNT(IID) AS ListCount FROM Inventors where DocumentHeaderIID = @DocumentHeaderIID";
                    command.Parameters.Add("@DocumentHeaderIID", documentHeader.IID);
                    int count = (int)command.ExecuteScalar();
                    int res;
                    int r = Math.DivRem(count, pageSize, out res);
                    if (res > 0)
                        r++;
                    return r;
                }
            }
        }

        public override List<Inventory> DocumentList(DocumentHeader documentHeader, string sortColumn, string sortOrder, int startRowIndex, int pageSize)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                using (SqlCeCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT TOP (" + pageSize +
                                          ") IID, DocumentHeaderIID, Barcode, QuantityOriginal, CreateDate, InputType, QuantityEdit, QuantityDifference, Product " +
                                          "FROM Inventors " +
                                          "WHERE  DocumentHeaderIID = @DocumentHeaderIID  and (IID NOT IN (SELECT     TOP (" + startRowIndex +
                                          ") IID FROM Inventors AS Inventors_1 where DocumentHeaderIID = @DocumentHeaderIID ";
                    if (sortColumn != string.Empty)
                        command.CommandText+="Order by " + sortColumn + " " + sortOrder+")) order by " + sortColumn + " " + sortOrder;
                    else
                    {
                        command.CommandText += "))";
                    }
                    command.Parameters.Add("@DocumentHeaderIID", documentHeader.IID);
                    return ReadFromCommand(command);
                }
            }
        }

    }
}
